USE [influenza]
GO

/****** Object:  StoredProcedure [dbo].[SP_datImpCedula]    Script Date: 02/08/2010 18:17:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[SP_datImpCedula]
@param varchar(50)
as
Begin
declare @qry varchar(8000)

SET @qry='select b.cFolio,a.cPaterno,a.cMaterno,a.cNombre,a.curp,
(a.nDias+''/''+a.nMeses+''/''+CAST(a.nAnio as varchar(4))) as FechaNac , 
sx.cSexo,
CASE a.nEmbarazada
     When ''1'' then ''SI'' 
     WHEN ''2'' then ''NO''
     WHEN ''3'' then ''NO SABE''
END as Embarazo,
a.nAfiliado,ns.cNacionalidad,loc.localidad,en.nombre as entidad, 
mun.cNmunicipio as cMunicipio,a.calle,a.calle1,a.calle2,a.cp,a.cCalle_numero,a.cColonia,a.telefono,
CASE a.nIndigena
     When ''1'' then ''SI'' 
     WHEN ''2'' then ''NO''
     WHEN ''3'' then ''NO SABE''
END as Indigena,
CASE a.nLenguaIndigena
     When ''1'' then ''SI'' 
     WHEN ''2'' then ''NO''
     WHEN ''3'' then ''NO SABE''
END as LIndigena,
oc.ocupacion,
sr.servicio,
CONVERT(varchar(10),b.fIngreso,103) as FechaIngreso,
CONVERT(varchar(10),b.fSintoma_inicial,103) as FechaInicial,
dig.diagnostico,
CASE b.nRecibioTratamiento
     When ''1'' then ''SI'' 
     WHEN ''2'' then ''NO''
     WHEN ''3'' then ''NO SABE''
END as RecibioTratamiento, 
CASE b.nTratamiento_Antimicrobianos
     When ''1'' then ''SI'' 
     WHEN ''2'' then ''NO''
     WHEN ''3'' then ''NO SABE''
END as TratamientoAntimicrobianos,
CASE b.nTratamiento_Antiviral
     When ''1'' then ''SI'' 
     WHEN ''2'' then ''NO''
     WHEN ''3'' then ''NO SABE''
END as TratamientoAntiviral,
atv.antivirales,
b.id_principal as idr 
from dat_generales a
inner join dat_registro b on (a.id_principal = b.id_generales) 
inner join Cat_Sexo sx on (a.nSexo=sx.id_principal)
inner join Cat_Nacionalidades ns on(a.nNacionalidad=ns.id_nacionalidad)
inner join Catalogos.dbo.cat_localidades loc on(a.nLocalidad=loc.id_localidad)
inner join Catalogos.dbo.entidad en on (a.nEntidad=en.entidad)
inner join Catalogos.dbo.municipios_inegi mun on (a.nMunicipio=mun.id_principal)
inner join cat_ocupacion oc on (a.nOcupacion=oc.id_ocupacion)
inner join cat_servicios sr on (b.id_servicio=sr.id_servicio)
inner join cat_diagnosticos dig on (b.nDiagnostico=dig.id_diagnostico)
inner join cat_antivirales atv on (b.nAntiviral=atv.id_antivirales)
where a.id_principal='+@param

exec (@qry)
End
GO

